from openpyxl import load_workbook
from urllib.request import Request
import urllib.request
import urllib.parse
# read words or sentence from file(excel)
file_path = r"test.xlsx"

wb = load_workbook(file_path)
ws = wb.active

for x in range(1, ws.max_row + 1):
    # 获取被翻译单词或句子
    cell = "A{0}".format(str(x))
    post_word = ws[cell].value

    # 被翻译单元格为空 略过
    if post_word is None:
        continue
    # 被翻译单元格值为空格 略过
    elif type(post_word) == "StringType":
        if post_word.strip == "":
            continue
    # 已经翻译过的 略过
    cell = "B{0}".format(str(x))
    if not ws[cell].value is None:
        continue
    # 翻译 DE to CN
    request_header = ("Content-Type", "application/json; charset=utf-8")
    post_url = "http://www.godic.net/Home/TranslationAjax"
    post_data = urllib.parse.urlencode({"to": "zh-CN", "from": "de", "text": post_word, "contentType": "text%2Fplain"}) \
        .encode(encoding='UTF8')
    req = Request(post_url, data=post_data)
    res = urllib.request.urlopen(req)
    content = res.read().decode("utf-8")
    # 返回数据写B列
    ws[cell].value = content

    # 打印进度
    print("{0}/{1}, {2} = {3}".format(str(x), str(ws.max_row + 1), post_word, content))

    # 保存文件
    wb.save(file_path)
